﻿using System;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.IO;
using System.Text;
using System.Threading;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using DTSRuntimeWrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using DTSExecResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult;
using DTSProductLevel = Microsoft.SqlServer.Dts.Runtime.DTSProductLevel;
using VariableDispenser = Microsoft.SqlServer.Dts.Runtime.VariableDispenser;
using System.Windows.Forms;
namespace PalProjects.SSIS.ControlFlow
{
    [DtsTask(
        DisplayName = "Drop Index",
        TaskType = "General",
        UITypeName = "PalProjects.SSIS.ControlFlow.DropIndexInterface,SSISDropIndex,Version=1.0.0.0,Culture=Neutral,PublicKeyToken=d371cbd476efc04b",
        IconResource = "SSISDropIndex.SSISDropIndex.ico",
        RequiredProductLevel = DTSProductLevel.None)]

    public class DropIndex : Task
    {
        #region -- Private Properties
        private string ddlCreateScript;
        private string ddlDropScript;
        #endregion

        #region --- Public Properties
        private string entityName;
        [Category("Custom"), Description("Entity Name")]
        public string EntityName
        {
            get { return entityName; }
            set { entityName = value; }
        }

        private string mdConnectionName;
        [Category("Custom"), Description("Metadata Connection Name")]
        public string MDConnectionName
        {
            get { return mdConnectionName; }
            set { mdConnectionName = value; }
        }

        private string mdConnectionString;
        [Category("Custom"), Description("Metadata Connection String")]
        public string MDConnectionString
        {
            get { return mdConnectionString; }
            set { mdConnectionString = value; }
        }

        private string scriptVariable;
        [Category("Custom"), Description("Script Variable")]
        public string ScriptVariable
        {
            get { return scriptVariable; }
            set { scriptVariable = value; }
        }
        #endregion

        #region --- Constructor
        public DropIndex()
        {

        }
        #endregion

        #region --- Task Implementation
        public override void InitializeTask(Connections connections, VariableDispenser variableDispenser, IDTSInfoEvents events, IDTSLogging log, EventInfos eventInfos,
            LogEntryInfos logEntryInfos, ObjectReferenceTracker refTracker)
        {
            base.InitializeTask(connections, variableDispenser, events, log, eventInfos, logEntryInfos, refTracker);

        }
        #endregion

        #region --- Validate
        public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {

            return DTSExecResult.Success;
        }
        #endregion

        #region --- Execute
        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            DataTable dataTable;
            using (SqlConnection sqlConnection = new SqlConnection(connections[mdConnectionName].ConnectionString))
            {
                SqlCommand sqlCommand = new SqlCommand("SELECT [name],type_desc,index_id FROM sys.indexes WHERE object_id = object_id('dbo." + entityName + "') AND [name] IS NOT NULL AND is_primary_key=0", sqlConnection);
                sqlCommand.CommandTimeout = 0;
                dataTable = new DataTable();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
                dataAdapter.Fill(dataTable);

                foreach (DataRow dataRow in dataTable.Rows)
                {
                    ddlDropScript += "DROP INDEX " + dataRow["name"].ToString() + " ON dbo." + entityName + "; ";
                    ddlCreateScript += "CREATE " + dataRow["type_desc"].ToString() + " INDEX " + dataRow["name"].ToString() + " ON dbo." + entityName + "(";
                    sqlCommand.CommandText = "SELECT b.[name],a.is_descending_key FROM sys.index_columns a JOIN sys.columns b ON a.column_id = b.column_id AND a.object_id = b.object_id AND a.object_id = object_id('dbo." + entityName + "') AND a.index_id = " + dataRow["index_id"].ToString() + " AND b.[name] IS NOT NULL";
                    dataAdapter.SelectCommand = sqlCommand;
                    DataTable dtIndexCols = new DataTable();
                    dataAdapter.Fill(dtIndexCols);
                    int colcount = 0;
                    foreach (DataRow idxcol in dtIndexCols.Rows)
                    {
                        colcount++;
                        ddlCreateScript += idxcol["name"].ToString() + (Convert.ToBoolean(idxcol["is_descending_key"]) ? " DESC" : " ASC");
                        if (colcount < dtIndexCols.Rows.Count)
                        {
                            ddlCreateScript += ",";
                        }
                        else
                        {
                            ddlCreateScript += "); ";
                        }
                    }
                }
            }

            if (!string.IsNullOrEmpty(ddlDropScript) && !string.IsNullOrEmpty(ddlCreateScript))
            {
                Variables containerVars = null;
                variableDispenser.LockOneForWrite(scriptVariable, ref containerVars);
                containerVars[scriptVariable].Value = ddlCreateScript;
                
                using (SqlConnection sqlConnection = new SqlConnection(connections[mdConnectionName].ConnectionString))
                {
                    SqlCommand sqlCommand = new SqlCommand(ddlDropScript, sqlConnection);
                    sqlCommand.CommandTimeout = 0;
                    sqlConnection.Open();
                    sqlCommand.ExecuteNonQuery();
                }
            }

            return DTSExecResult.Success;
        }

        #endregion
    }
}
